from django.shortcuts import *
from web.models import *
import pymysql
# Create your views here.
def conn():
    nn = pymysql.connect(host='127.0.0.1'  # 连接名称，默认127.0.0.1
                         , user='root'  # 用户名
                         , password='123456'  # 密码
                         , port=3306  # 端口,默认为：3306
                         , database='ceshi'  # 数据库名称
                         , charset='utf8')  # 字符编码
    return nn
nn = conn() #调用方法
cursor = nn.cursor() #创建游标
def index(request):

    # all_stu = Stu.objects.all()
    sql = 'select * from web_stu'
    cursor.execute(sql)  # 执行语句
    all_stu = cursor.fetchall()  # 通过fetchall方法获得数据
    return render(request,'index.html',{'all_stu':all_stu})


def add(request):
    if request.method == 'GET':
        return render(request,'add.html')
    else:
        name=request.POST.get('name')
        sex=request.POST.get('sex')
        clas=request.POST.get('clas')
        age=request.POST.get('age')
        # Stu_new = Stu(StuName=name,StuSex=sex,StuClass=clas,StuAge=age)
        cursor.execute("INSERT INTO web_stu (StuName,StuSex,StuClass,StuAge) VALUES ('{}','{}','{}','{}')".format(name,sex,clas,age))
        nn.commit()
        # Stu_new.save()
        return redirect('index')



def delete(request,id):
    # get_Stu = Stu.objects.filter(pk=id)
    # get_Stu.delete()
    cursor.execute("delete from web_stu where id='{}'".format(id))
    nn.commit()
    return redirect('index')


def update(request,id):
    if request.method == 'GET':
        get_Stu = Stu.objects.filter(pk=id).first()
        return render(request, 'update.html',{'get_Stu':get_Stu})
    else:
        name = request.POST.get('name')
        sex = request.POST.get('sex')
        clas = request.POST.get('clas')
        age = request.POST.get('age')
        # Stu.objects.filter(pk=id).update(StuName=name,StuSex=sex,StuClass=clas,StuAge=age)
        # cursor.execute("INSERT INTO web_stu (StuName,StuSex,StuClass,StuAge) VALUES ('{}','{}','{}','{}')".format(name,sex,clas,age))

        cursor.execute("update web_stu set StuName ='{}',StuSex='{}',StuClass='{}',StuAge='{}' where id='{}'".format(name,sex,clas,age,id))
        nn.commit()
        return redirect('index')